﻿using System.Reflection;
using System.Text.RegularExpressions;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

namespace AutoTest.Common.ExcelExport
{
    /// <summary>
    /// 导出帮助类
    /// </summary>
    public class ExportHelper
    {
        /// <summary>
        /// 获得WorkBook对象
        /// </summary>
        /// <param name="ms">文件流</param>
        /// <param name="isxlsx">是否是xlsx格式</param>
        /// <returns></returns>
        public static IWorkbook GetWorkBook(Stream ms = null!, bool isxlsx = true)
        {
            IWorkbook workbook;
            if (isxlsx)
            {
                workbook = ms != null ? new XSSFWorkbook(ms) : new XSSFWorkbook();
            }
            else
            {
                workbook = ms != null ? new HSSFWorkbook(ms) : new HSSFWorkbook();
            }
            return workbook;
        }
        /// <summary>
        /// 渲染表格
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sheet"></param>
        /// <param name="rowIndex"></param>
        /// <param name="colIndex"></param>
        /// <param name="dataList"></param>
        /// <param name="prevColumns"></param>
        /// <param name="lastColumns"></param>
        public static void RenderSheet<T>(ISheet sheet, int rowIndex, int colIndex, IEnumerable<T> dataList, List<ExportHeaderAttribute> prevColumns = null!, List<ExportHeaderAttribute> lastColumns = null!)
        {
            List<ExcelColumnOption> optionList = GetAllRenderOption<T>(prevColumns, lastColumns);
            var result = WriteHeader(sheet, rowIndex, colIndex, optionList);
            rowIndex = result.Item1;
            var newOptionList = result.Item2;
            if (dataList?.Any() ?? false)
            {
                foreach (var dataItem in dataList)
                {
                    rowIndex++;
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    IDataFormat format = sheet.Workbook.CreateDataFormat();
                    foreach (var option in newOptionList)
                    {
                        ICell cell = dataRow.CreateCell(option.Col);
                        ICellStyle style = sheet.Workbook.CreateCellStyle();

                        if (!string.IsNullOrEmpty(option.DataFormat))
                        {
                            style.DataFormat = format.GetFormat(option.DataFormat);
                        }

                        switch (option.ColumnType)
                        {
                            case ColumnType.Formula:
                                var reg = new Regex("\\$\\{\\w+\\}");
                                string formula = option.Content;
                                var content = reg.Replace(formula, (match) =>
                                {
                                    string pname = match.Value.Replace("${", "").Replace("}", "");
                                    var opt = newOptionList.FirstOrDefault(c => c.Property.Name == pname);
                                    return $"{ToNumberSystem26((opt?.Col ?? 0) + 1)}{rowIndex}";
                                });
                                cell.SetCellFormula(content);
                                break;
                            default:
                                cell.SetCellValue(option.Property.GetValue(dataItem)?.ToString() ?? "");
                                break;
                        }

                    }
                }
            }
        }

        /// <summary>
        /// 获得表格的配置
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="prevColumns"></param>
        /// <param name="lastColumns"></param>
        /// <returns></returns>
        protected static List<ExcelColumnOption> GetAllRenderOption<T>(List<ExportHeaderAttribute> prevColumns, List<ExportHeaderAttribute> lastColumns = null!)
        {
            var optionList = new List<ExcelColumnOption>();
            int prevCount = 0;
            if (prevColumns != null && prevColumns.Count > 0)
            {
                prevCount = prevColumns.Count;
                for (int i = 0; i < prevColumns.Count; i++)
                {
                    var item = prevColumns[i];
                    optionList.Add(new ExcelColumnOption()
                    {
                        BackgroundColor = item.BackgroundColor,
                        Col = item.DeviationCol ?? i,
                        Colspan = item.Colspan,
                        ColumnType = item.ColumnType,
                        Content = item.Content,
                        DataFormat = item.DataFormat,
                        FirstRowStyle = item.FirstRowStyle,
                        FontColor = item.FontColor,
                        HeaderName = item.HeaderName,
                        Rowspan = item.Rowspan,
                        TopHeaderName = item.TopHeaderName
                    });
                }
                int max = optionList.Max(c => c.Col);
                prevCount = prevCount > max ? prevCount : max;
            }

            List<ExcelColumnOption> classOptionList = GetClassPropertyAttributes<T>(prevCount);
            if (classOptionList != null && classOptionList.Count > 0)
            {
                optionList.AddRange(classOptionList);
                int max = optionList.Max(c => c.Col);
                prevCount = prevCount > max ? prevCount : max;
            }

            if (lastColumns != null && lastColumns.Count > 0)
            {
                for (int i = 0; i < lastColumns.Count; i++)
                {
                    var item = lastColumns[i];
                    optionList.Add(new ExcelColumnOption()
                    {
                        BackgroundColor = item.BackgroundColor,
                        Col = prevCount + (item.DeviationCol ?? i),
                        Colspan = item.Colspan,
                        ColumnType = item.ColumnType,
                        Content = item.Content,
                        DataFormat = item.DataFormat,
                        FirstRowStyle = item.FirstRowStyle,
                        FontColor = item.FontColor,
                        HeaderName = item.HeaderName,
                        Rowspan = item.Rowspan,
                        TopHeaderName = item.TopHeaderName
                    });
                }
            }



            return optionList;
        }

        /// <summary>
        /// 添加和更新需要合并的单元格
        /// </summary>
        /// <param name="dict"></param>
        /// <param name="topheadername"></param>
        /// <param name="row"></param>
        /// <param name="col"></param>
        protected static void AddOrUpdateMergeDict(Dictionary<string, CellRangeAddress> dict, string topheadername, int row, int col)
        {
            if (dict.TryGetValue(topheadername, out CellRangeAddress? address))
            {
                if (address != null)
                {
                    if (address.FirstRow > row) address.FirstRow = row;
                    if (address.LastRow < row) address.LastRow = row;
                    if (address.FirstColumn > col) address.FirstColumn = col;
                    if (address.LastColumn < col) address.LastColumn = col;
                    dict[topheadername] = address;
                }
                else
                {
                    dict[topheadername] = new CellRangeAddress(row, row, col, col);
                }

            }
            else
            {
                dict[topheadername] = new CellRangeAddress(row, row, col, col);
            }
        }

        /// <summary>
        /// 渲染头部表头
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="startRow"></param>
        /// <param name="startCol"></param>
        /// <param name="optionList"></param>
        /// <returns></returns>
        protected static Tuple<int, List<ExcelColumnOption>> WriteHeader(ISheet sheet, int startRow, int startCol, List<ExcelColumnOption> optionList)
        {
            IRow currentRow = GetRow(sheet, startRow);
            Dictionary<string, CellRangeAddress> mergerHeaderDict = [];
            List<ExcelColumnOption> newOptionList = [];
            int rowIndex = startRow;
            int headCount = 1;
            IDataFormat format = sheet.Workbook.CreateDataFormat();
            if (optionList != null && optionList.Count > 0)
            {
                for (int i = 0; i < optionList.Count; i++)
                {
                    ExcelColumnOption option = optionList[i];
                    int col = option.Col + startCol;

                    if (option.Colspan > 1 || option.Rowspan > 1)
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(startRow, startRow + (option.Rowspan < 1 ? 0 : option.Rowspan - 1), col, col + (option.Colspan < 1 ? 0 : option.Colspan - 1)));
                        // if (rowIndex + option.Rowspan > rowIndex) rowIndex = rowIndex + option.Rowspan - 1;
                        if (headCount < option.Rowspan) headCount = option.Rowspan;
                    }

                    ICell headerCell = null!; //GetCell(currentRow, col);

                    if (!string.IsNullOrWhiteSpace(option.TopHeaderName))
                    {
                        List<ExcelColumnOption> subList = optionList.Where(c => c.TopHeaderName == option.TopHeaderName).ToList();
                        if (subList != null)
                        {
                            switch (option.FirstRowStyle)
                            {
                                case EnumFirstHeaderStyle.NextRow:
                                    //合并行在下一行，在循环的一行在当前行
                                    headerCell = GetCell(currentRow, col);
                                    AddOrUpdateMergeDict(mergerHeaderDict, option.TopHeaderName, startRow + (option.Rowspan < 1 ? 1 : option.Rowspan), col);
                                    break;
                                case EnumFirstHeaderStyle.PrevRow:
                                    headerCell = GetCell(currentRow, col);
                                    AddOrUpdateMergeDict(mergerHeaderDict, option.TopHeaderName, startRow - 1, col);
                                    //合并的行在上一行
                                    break;
                                default:
                                    //合并的行在当前行，则循环的一行，应该在下一行开始循环
                                    IRow nextRow = GetRow(sheet, startRow + (option.Rowspan < 1 ? 1 : option.Rowspan));
                                    headerCell = nextRow.CreateCell(col);
                                    AddOrUpdateMergeDict(mergerHeaderDict, option.TopHeaderName, startRow, col);
                                    int count = option.Rowspan + 1;
                                    if (count > headCount) headCount = count;
                                    break;
                            }
                        }
                    }
                    headerCell ??= GetCell(currentRow, col);

                    ICellStyle style = sheet.Workbook.CreateCellStyle();
                    style.VerticalAlignment = VerticalAlignment.Center;
                    style.Alignment = HorizontalAlignment.Center;

                    if (option.BackgroundColor.HasValue)
                    {
                        style.FillPattern = FillPattern.SolidForeground;
                        style.FillForegroundColor = option.BackgroundColor ?? HSSFColor.DarkBlue.Index;
                    }
                    if (option.FontColor.HasValue)
                    {
                        IFont font = sheet.Workbook.CreateFont();
                        font.FontHeightInPoints = 9;
                        font.Color = HSSFColor.White.Index;
                        font.FontName = "Arial";
                        style.SetFont(font);
                    }
                    headerCell.CellStyle = style;
                    headerCell.SetCellValue(option.HeaderName);

                    option.Col = col;
                    newOptionList.Add(option);

                }
            }

            if (mergerHeaderDict.Count > 0)
            {
                foreach (var item in mergerHeaderDict)
                {
                    IRow row = GetRow(sheet, item.Value.FirstRow); //sheet.GetRow(item.Value.FirstRow);
                    ICell cell = GetCell(row, item.Value.FirstColumn);
                    cell.SetCellValue(item.Key);
                    sheet.AddMergedRegion(item.Value);
                    var option = optionList?.FirstOrDefault(c => c.TopHeaderName == item.Key)!;
                    ICellStyle style = sheet.Workbook.CreateCellStyle();
                    style.VerticalAlignment = VerticalAlignment.Center;
                    style.Alignment = HorizontalAlignment.Center;
                    if (!string.IsNullOrEmpty(option?.DataFormat))
                    {
                        style.DataFormat = format.GetFormat(option.DataFormat);
                    }
                    if (option?.BackgroundColor.HasValue ?? false)
                    {
                        style.FillPattern = FillPattern.SolidForeground;
                        style.FillForegroundColor = option.BackgroundColor ?? HSSFColor.DarkBlue.Index;
                    }



                    if (option?.FontColor.HasValue??false)
                    {
                        IFont font = sheet.Workbook.CreateFont();
                        font.FontHeightInPoints = 9;
                        font.Color = HSSFColor.White.Index;
                        font.FontName = "Arial";
                        style.SetFont(font);
                    }
                    cell.CellStyle = style;
                    cell.SetCellValue(item.Key);
                }
            }

            return new Tuple<int, List<ExcelColumnOption>>(rowIndex + headCount - 1, newOptionList);
        }
        /// <summary>
        /// 获得行
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="rowIndex"></param>
        /// <returns></returns>
        private static IRow GetRow(ISheet sheet, int rowIndex)
        {
            var row = sheet.GetRow(rowIndex);
            row ??= sheet.CreateRow(rowIndex);
            return row;
        }

        /// <summary>
        /// 获得列
        /// </summary>
        /// <param name="row"></param>
        /// <param name="colIndex"></param>
        /// <returns></returns>
        private static ICell GetCell(IRow row, int colIndex)
        {
            ICell cell = row.GetCell(colIndex);
            cell ??= row.CreateCell(colIndex);
            return cell;
        }


        /// <summary>
        /// 获得表头
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        protected static List<ExcelColumnOption> GetClassPropertyAttributes<T>(int prevCount)
        {
            var list = new List<ExcelColumnOption>();
            Type targetType = typeof(T);
            PropertyInfo[] properties = targetType.GetProperties();
            if (properties != null && properties.Length > 0)
            {
                for (int i = 0; i < properties.Length; i++)
                {
                    PropertyInfo property = properties[i];
                    if (property == null) continue;

                    ExportIgnoreAttribute ignoreAttribute = property.GetCustomAttribute<ExportIgnoreAttribute>(false)!;
                    if (ignoreAttribute != null && ignoreAttribute.Ignore) continue;

                    ExportHeaderAttribute attribute = property.GetCustomAttribute<ExportHeaderAttribute>(false)!;

                    ExcelColumnOption option;
                    if (attribute != null)
                    {
                        option = new ExcelColumnOption()
                        {
                            Col = prevCount + (attribute.DeviationCol ?? i),
                            Colspan = attribute.Colspan,
                            ColumnType = attribute.ColumnType,
                            Content = attribute.Content,
                            DataFormat = attribute.DataFormat,
                            TopHeaderName = attribute.TopHeaderName,
                            Property = property,
                            Rowspan = attribute.Rowspan,
                            HeaderName = attribute.HeaderName,
                            FirstRowStyle = attribute.FirstRowStyle,
                            BackgroundColor = attribute.BackgroundColor,
                            FontColor = attribute.FontColor

                        };
                    }
                    else
                    {
                        option = new ExcelColumnOption()
                        {
                            Col = prevCount + i + 1,
                            Colspan = 1,
                            ColumnType = ColumnType.Text,
                            Content = "",
                            DataFormat = "",
                            HeaderName = property.Name,
                            Property = property,
                            Rowspan = 1,
                            TopHeaderName = string.Empty,
                            FirstRowStyle = EnumFirstHeaderStyle.CurrentRow,
                            BackgroundColor = HSSFColor.DarkBlue.Index,
                            FontColor = HSSFColor.White.Index
                        };
                    }
                    list.Add(option);
                }
            }
            return [.. list.OrderBy(c => c.Col)];
        }


        /// <summary>
        /// 将指定的26进制表示转换为自然数。映射关系：[A-Z] ->[1-26]。
        /// </summary>
        /// <param name="s">26进制表示（如果无效，则返回0）。</param>
        /// <returns>自然数。</returns>
        public static int FromNumberSystem26(string s)
        {
            if (string.IsNullOrEmpty(s)) return 0;
            int n = 0;
            for (int i = s.Length - 1, j = 1; i >= 0; i--, j *= 26)
            {
                char c = Char.ToUpper(s[i]);
                if (c < 'A' || c > 'Z') return 0;
                n += ((int)c - 64) * j;
            }
            return n;
        }
        /// <summary>
        /// 将指定的自然数转换为26进制表示。映射关系：[1-26] ->[A-Z]。
        /// </summary>
        /// <param name="n">自然数（如果无效，则返回空字符串）。</param>
        /// <returns>26进制表示。</returns>
        public static string ToNumberSystem26(int n)
        {
            string s = string.Empty;
            while (n > 0)
            {
                int m = n % 26;
                if (m == 0) m = 26;
                s = (char)(m + 64) + s;
                n = (n - m) / 26;
            }
            return s;
        }


    }

    /// <summary>
    /// 配置
    /// </summary>
    public class ExcelColumnOption
    {
        public EnumFirstHeaderStyle FirstRowStyle { get; set; } = EnumFirstHeaderStyle.CurrentRow;
        public string TopHeaderName { get; set; } = "";
        public string HeaderName { get; set; } = "";
        /// <summary>
        /// 第几列
        /// </summary>
        public int Col { get; set; }

        /// <summary>
        /// 列号（对应A,B,C,D)
        /// </summary>
        public string ColName { get { return ExportHelper.ToNumberSystem26(Col); } }

        /// <summary>
        /// 对应属性名称
        /// </summary>
        public PropertyInfo Property { get; set; } = null!;

        /// <summary>
        /// 占多少列
        /// </summary>
        public int Colspan { get; set; } = 1;
        /// <summary>
        /// 占多少行
        /// </summary>
        public int Rowspan { get; set; } = 1;

        /// <summary>
        /// 单元格内容
        /// </summary>
        public string Content { get; set; } = string.Empty;

        /// <summary>
        /// 数据格式
        /// </summary>
        public string DataFormat { get; set; } = "";

        /// <summary>
        /// 类型
        /// </summary>
        public ColumnType ColumnType { get; set; } = ColumnType.Text;

        /// <summary>
        /// 单元格颜色
        /// </summary>
        public short? BackgroundColor { get; set; } = HSSFColor.DarkBlue.Index;

        public short? FontColor { get; set; } = HSSFColor.White.Index;
    }
}
